﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.OleDb;
namespace DAO
{
    public class MonHocDAO
    {
        public static MonHocDTO LayPhongTuDataRow(DataRow dr)
        {
            MonHocDTO item = null;
            try
            {
                item = new MonHocDTO();
                item.ID = dr["ID"].ToString();
                item.TenMonHoc = dr["TenMonHoc"].ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy thông tin: " + ex.ToString());
            }
            return item;
        }

        //lấy danh sách đầy đủ
        public static DataTable LayDanhSachMHTheoLop(string idLop)
        {
            DataTable dt = null;
            try
            {
                String query = "SELECT mh.TenMonHoc, kmh.SoTietTrongTuan, (Select Count(*) From ChiTietThoiKhoaBieu ct, ThoiKhoaBieu tkb "+
                "Where ct.IDThoiKhoaBieu = tkb.ID and mh.ID = ct.IDMonHoc and tkb.IDLop = l.ID) "
                + "From MonHoc mh, Khoi_MonHoc kmh, Lop l "
                + "Where l.ID = ? and l.IDKhoi = kmh.IDKhoi and mh.ID = kmh.IDMonHoc ";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@ID", idLop));

                dt = DataProvider.ExecuteNonQuery(query, arrParameter);

            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return dt;
        }

        public static DataTable LayDanhSachMHTheoKhoi(string idKhoi)
        {
            DataTable dt = null;
            try
            {
                String query = "SELECT mh.TenMonHoc, kmh.SoTietTrongTuan, (Select Count(*) From ChiTietThoiKhoaBieu ct, ThoiKhoaBieu tkb " +
                "Where ct.IDThoiKhoaBieu = tkb.ID and mh.ID = ct.IDMonHoc and tkb.IDLop = l.ID) "
                + "From MonHoc mh, Khoi_MonHoc kmh, Lop l "
                + "Where l.IDKhoi = ? and l.IDKhoi = kmh.IDKhoi and mh.ID = kmh.IDMonHoc ";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@ID", idKhoi));

                dt = DataProvider.ExecuteNonQuery(query, arrParameter);

            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return dt;
        }


        public static DataTable LayDanhSachMHTheoKhoi_Mon(string idLop)
        {
            DataTable dt = null;
            try
            {
                String query = "SELECT mh.ID, kmh.SoTietTrongTuan, kmh.SoTietTrenBuoi From MonHoc mh, Khoi_MonHoc kmh, Lop l "
                + " Where mh.ID = kmh.IDMonHoc and l.IDKhoi = kmh.IDKhoi and l.ID = ?";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@ID", idLop));

                dt = DataProvider.ExecuteNonQuery(query, arrParameter);

            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return dt;
        }
        public static string LayTenMonHoc(string id)
        {
            string ten = null;
            try
            {
                String query = "select mh.TenMonHoc From MonHoc mh where mh.id = ?";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@ID", id));
                DataTable dt = DataProvider.ExecuteNonQuery(query, arrParameter);
                ten = dt.Rows[0][0].ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return ten;
        }

    }
}
